package com.weifly.jettycn.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.weifly.jettycn.TableConst;
import com.weifly.jettycn.domain.Release;
import com.weifly.jettycn.util.QueryInfo;

public class ReleaseDao extends JdbcDaoSupport {

	/**
	 * 获得release列表
	 * 
	 * @param queryInfo
	 * @param catalogList
	 * @return
	 */
	public List<Release> queryReleaseList(String projectid, QueryInfo queryInfo){
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(TableConst.TABLE_RELEASE);
		sb.append(" where c_prjid=? order by d_publishdate desc");
		
		String sql = sb.toString();
		
		final List<Release> releaseList = new ArrayList<Release>();
		this.getJdbcTemplate().query(sql, new Object[]{projectid}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				while(rs.next()){
					Release r = populateRelease(rs);
					releaseList.add(r);
				}
				return null;
			}
			
		});
		return releaseList;
	}
	
	private Release populateRelease(ResultSet rs) throws SQLException{
		Release r = new Release();
		r.setReleaseid(rs.getString("c_releaseid"));
		r.setPrjid(rs.getString("c_prjid"));
		r.setVersion(rs.getString("c_version"));
		r.setPublishdate(rs.getDate("d_publishdate"));
		r.setDesc(rs.getString("c_desc"));
		return r;
	}
	
	public int getReleaseCountor(String projectid){
		String sql = "select count(*) from " + TableConst.TABLE_RELEASE + " where c_prjid=?";
		
		Integer totalCount = this.getJdbcTemplate().query(sql, new Object[]{projectid}, new ResultSetExtractor<Integer>(){

			public Integer extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()){
					return rs.getInt(1);
				}
				return null;
			}
			
		});
		return totalCount.intValue();
	}

	/**
	 * 插入release
	 */
	public void insertRelease(final Release release){
		String sql = "insert into " + TableConst.TABLE_RELEASE + "(c_releaseid, c_prjid, c_version, d_publishdate, c_desc) values(?,?,?,?,?)";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, release.getReleaseid());
				ps.setString(2, release.getPrjid());
				ps.setString(3, release.getVersion());
				ps.setDate(4, new java.sql.Date(release.getPublishdate().getTime()));
				ps.setString(5, release.getDesc());
			}
			
		});
	}
	
	public Release getRelease(String releaseid){
		String sql = "select * from " + TableConst.TABLE_RELEASE + " where c_releaseid=?";
		return this.getJdbcTemplate().query(sql, new Object[]{releaseid}, new ResultSetExtractor<Release>(){

			public Release extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()){
					return populateRelease(rs);
				}
				return null;
			}
			
		});
	}
	
	public void updateRelease(final Release release){
		String sql = "update " + TableConst.TABLE_RELEASE + " set c_version=?, d_publishdate=?, c_desc=? where c_releaseid=?";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, release.getVersion());
				ps.setDate(2, new java.sql.Date(release.getPublishdate().getTime()));
				ps.setString(3, release.getDesc());
				ps.setString(4, release.getReleaseid());
			}
			
		});
	}
	
	public void deleteRelease(String releaseid){
		String sql = "delete from " + TableConst.TABLE_RELEASE + " where c_releaseid=?";
		this.getJdbcTemplate().update(sql, new Object[]{releaseid});
	}
}
